﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;
using log4net;
using Org.SqlHelper;
using System.Data.OracleClient;
using System.Data.Odbc;

namespace QualityRpt.Utils
{
    /// <summary>
    /// PageUtils 的摘要说明
    /// </summary>
    public class PageUtils
    {
        static ILog logger = LogManager.GetLogger("PageUtils");
        public PageUtils()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        int page = 1; // 页号

        long totalNum = 0; // 记录总数

        int perPageNum = 20; // 每页显示记录数

        int allPage = 1; // 总页数

        int cpage = 1; //

        int firstResult = 1; // 开始记录数

        string totalCountSql = string.Empty;

        string listSql = string.Empty;

        public PageUtils(int page, long totalNum, int perPageNum)
        {
            this.page = page;
            this.totalNum = totalNum;
            this.perPageNum = perPageNum;
            this.ExecuteCount();
        }
        public int Page
        {
            get { return page; }
            set { page = value; }
        }
        public long TotalNum
        {
            get { return totalNum; }
            set { totalNum = value; }
        }
        public int PerPageNum
        {
            get { return perPageNum; }
            set { perPageNum = value; }
        }
        public int AllPage
        {
            get { return allPage; }
            set { allPage = value; }
        }
        public int FirstResult
        {
            get { return firstResult; }
            set { firstResult = value; }
        }
        private int _endResult;

        public int EndResult
        {
            get { return _endResult; }
            set { _endResult = value; }
        }

        public string TotalCountSql
        {
            get { return totalCountSql; }
            set { totalCountSql = value; }
        }
        public string ListSql
        {
            get { return listSql; }
            set { listSql = value; }
        }
        /// <summary>
        /// 计算结果集
        /// </summary>
        public void ExecuteCount()
        {
            this.allPage = (int)Math.Ceiling((double)((this.totalNum + this.perPageNum - 1) / this.perPageNum));
            int intPage = this.page;
            if (intPage > this.allPage)
            {
                this.cpage = 1;
            }
            else
            {
                this.cpage = intPage;
            }
            this.firstResult = (this.cpage - 1) * this.perPageNum;
            int _temp_end = this.firstResult + this.perPageNum;
            this._endResult = this.page > 1 ? (_temp_end - 1) : _temp_end;
        }
        /// <summary>
        /// 获得分页的html
        /// </summary>
        /// <param name="pageurl">pager.aspx?p={page}这样的字符串</param>
        /// <returns>
        /// <a href="#" class="readmore current">1</a> <a href="#" class="readmore">2</a>
        /// </returns>
        public string GenStPagination(string pageurl)
        {
            StringBuilder sbPager = new StringBuilder();
            if (this.Page > 1)
            {
                int prev = this.Page - 1;
                sbPager.Append("<a href=\"" + pageurl.Replace("{page}", prev.ToString()) + "\" class=\"readmore\">&lt;</a>");
                if (this.Page > 10)//如果离第一页太远的话，则加入...和第一页的链接
                {
                    sbPager.Append("<a href=\"" + pageurl.Replace("{page}", "1") + "\" class=\"readmore\">1</a><span class=\"disabled\">...</span> ");
                }
            }
            int fp = this.Page - 3;
            if (fp < 1)
            {
                fp = 1;
            }
            int lp = this.Page + 3;
            if (lp > this.AllPage)
            {
                lp = this.AllPage;
            }
            for (int i = fp; i <= lp; i++)
            {
                if (i == this.Page)
                {
                    sbPager.Append("<span>" + this.Page + "</span>");
                }
                else
                {
                    sbPager.Append("<a href=\"" + pageurl.Replace("{page}", i.ToString()) + "\" class=\"readmore\">" + i + "</a>");
                }
            }
            if (this.Page < this.AllPage)
            {
                if (this.AllPage - this.Page > 5)//离最后一页太远的话,则加入..和最后一页的链接
                {
                    sbPager.Append("<span class=\"disabled\">...</span>");
                    sbPager.Append("<a href=\"" + pageurl.Replace("{page}", this.AllPage.ToString()) + "\" class=\"readmore\">" + this.AllPage + "</a>");
                }
                int next = this.Page + 1;
                sbPager.Append("<a href=\"" + pageurl.Replace("{page}", next.ToString()) + "\" class=\"readmore\">&gt;</a>");
            }
            return sbPager.ToString();
        }
        /// <summary>
        /// get page list support sql2005 or high
        /// </summary>
        /// <returns></returns>
        public DataTable GetSql2005PageList()
        {
            using (SqlConnection conn = SqlServerHelper.GetConnection())
            {
                object objCount = SqlServerHelper.ExecuteScalar(conn, CommandType.Text, this.TotalCountSql);
                if ((Object.Equals(objCount, null)) || (Object.Equals(objCount, System.DBNull.Value)))
                {
                    this.TotalNum = 0;
                }
                else
                {
                    this.TotalNum = int.Parse(objCount.ToString());
                }
                ExecuteCount();//计算总共多少页,当前页的第一条记录
				if (!ListSql.ToLower().Contains("order by"))
                {
                    throw new Exception("Order by clause must contain in list sql");
                }
                else
                {
                    /*SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY id DESC)AS Page_Row, * FROM de_video) as list WHERE Page_Row between 20 and 40; */
					int start_idx = ListSql.ToLower().LastIndexOf("order by ");
					string _order = ListSql.Substring(start_idx);//获得order by 子句
					string _subSql = ListSql.Substring(6, start_idx - 6);//获得list sql 去掉select 和 order by后的字符串
                    int _temp_end = FirstResult + PerPageNum;
                    int endResult = page > 1 ? (_temp_end - 1) : _temp_end;
                    string nsql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (" + _order + ")AS PU_ROW," + _subSql + ") as PU_LIST WHERE PU_ROW BETWEEN " + FirstResult + " AND " + endResult;
                    //logger.Info(nsql);
                    return SqlServerHelper.ReadTable(conn, CommandType.Text, nsql);
                }
            }
        }
        /// <summary>
        /// get page list support oracle 10g
        /// select * from (select a1.*,rownum rn from(select * from emp) a1 where rownum<=10) where rn>=6; 
        /// </summary>
        /// <returns></returns>
        public DataTable GetOraclePageList()
        {
            using (OracleConnection conn = OracleHelper.GetConnection())
            {
                object objCount = OracleHelper.ExecuteScalar(conn, CommandType.Text, TotalCountSql);
                if ((Object.Equals(objCount, null)) || (Object.Equals(objCount, System.DBNull.Value)))
                {
                    this.TotalNum = 0;
                }
                else
                {
                    this.TotalNum = int.Parse(objCount.ToString());
                }
                ExecuteCount();//计算总共多少页,当前页的第一条记录
                if (!ListSql.ToLower().Contains("order by"))
                {
                    throw new Exception("Order by clause must contain in list sql");
                }
                else
                {
					int start_idx = ListSql.ToLower().LastIndexOf("order by ");
					string _order = ListSql.Substring(start_idx);//获得order by 子句
					string _subSql = ListSql.Substring(6, start_idx - 6);//获得list sql 去掉select 和 order by后的字符串
                    int _temp_end = FirstResult + PerPageNum;
                    int endResult = page > 1 ? (_temp_end - 1) : _temp_end;
                    string nsql = "select * from (select p_al.*,rownum p_rn from(select " + _subSql + " " + _order + ") p_al) where p_rn <= " + endResult + " AND p_rn>=" + firstResult;
                    //logger.Error(nsql);
                    return OracleHelper.ReadTable(conn, CommandType.Text, nsql);
                }
            }
        }
        /// <summary>
        /// get page list support db2
        /// SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20 
        /// </summary>
        /// <returns></returns>
        public DataTable GetDb2OdbcPageList()
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                //logger.Info(TotalCountSql + " WITH UR");
                object objCount = ODBCHelper.ExecuteScalar(conn, CommandType.Text, TotalCountSql+" WITH UR");
                if ((Object.Equals(objCount, null)) || (Object.Equals(objCount, System.DBNull.Value)))
                {
                    this.TotalNum = 0;
                }
                else
                {
                    this.TotalNum = int.Parse(objCount.ToString());
                }
                ExecuteCount();//计算总共多少页,当前页的第一条记录
                if (!ListSql.ToLower().Contains("order by"))
                {
                    throw new Exception("Order by clause must contain in list sql");
                }
                else
                {
					int start_idx = ListSql.ToLower().LastIndexOf("order by ");
					string _order = ListSql.Substring(start_idx);//获得order by 子句
					string _subSql = ListSql.Substring(6, start_idx - 6);//获得list sql 去掉select 和 order by后的字符串
                    int _temp_end = FirstResult + PerPageNum;
                    int endResult = page > 1 ? (_temp_end - 1) : _temp_end;
                    string nsql = "SELECT * FROM (SELECT ROWNUMBER() OVER (" + _order + ") AS PU_ROW," + _subSql + ") as PU_LIST WHERE PU_ROW>= " + FirstResult + " AND PU_ROW<=" + endResult +" FETCH FIRST "+PerPageNum+" ROWS ONLY";
                    //logger.Info(nsql);
                    DataTable pageTable =  ODBCHelper.ReadTable(conn, CommandType.Text, nsql);
                    pageTable.Columns.Remove("PU_ROW");
                    return pageTable;
                }
            }
        }
        public void DB2OdbcExecutePage()
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                object objCount = ODBCHelper.ExecuteScalar(conn, CommandType.Text, TotalCountSql + " WITH UR");
                if ((Object.Equals(objCount, null)) || (Object.Equals(objCount, System.DBNull.Value)))
                {
                    this.TotalNum = 0;
                }
                else
                {
                    this.TotalNum = int.Parse(objCount.ToString());
                }
                ExecuteCount();//计算总共多少页,当前页的第一条记录
            }
        }
    }
}
